package com.wiserp.rest.helper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.wiserp.rest.model.OpResult;
import com.wiserp.rest.model.SuppInv;
import com.wiserp.rest.model.SuppInvItem;

public class SuppInvHelper extends JdbcDaoSupport implements INounHelper {
	private static final String SQL_GET_UNPOSTED_SUPPINV = "SELECT a.hba001,a.hba007,b.bca002,a.hba005 FROM shb01 a LEFT JOIN sad01 b ON(a.hba007=b.bca001) WHERE a.hba003='4d' AND a.hba006='4' AND a.hba100='a' ";
	private static final String SQL_GET_UNPOSTED_COUNT_SUPPINV = "SELECT COUNT(*) FROM shb01 WHERE hba003='4d' AND hba006='4' AND hba100='a' ";
	private static final String SQL_GET_SUPPINVITEM_BY_FLOWNO = "SELECT a.hbb001,a.hbb004,(b.gbb006-b.gbb052) qty,a.hbb017,a.hbb021 FROM shb02 a LEFT JOIN sgb02 b ON(a.hbb006=b.gbb002 AND a.hbb007=b.gbb001) WHERE a.hbb002=?";
	private static final String SQL_GET_SUPPINV_BY_FLOWNO = "SELECT a.hba001,a.hba007,b.bca002,a.hba005 FROM shb01 a LEFT JOIN sad01 b ON(a.hba007=b.bca001) WHERE a.hba001=? AND a.hba006='4' AND  a.hba100='a' ";
	private static final String SQL_UPDATE_SUPPINVITEM = "UPDATE shb02 SET hbb023=?,hbb018=?,hbb021=?,hbb003=?,hbb015=hbb014*?,hbb031=hbb030*?,hbb034=hbb033*? WHERE hbb001=? AND hbb002=?";

	private PlatformTransactionManager txManager;
	
	
	public List findUnpost() {
		return this.getJdbcTemplate().query(SQL_GET_UNPOSTED_SUPPINV,
				new RowMapper<SuppInv>() {
					public SuppInv mapRow(ResultSet rs, int i)
							throws SQLException {
						SuppInv data = new SuppInv();
						data.setFlowNo(rs.getString("hba001"));
						data.setSuppNo(rs.getString("hba007"));
						data.setSuppName(rs.getString("bca002"));
						data.setBillDate(rs.getDate("hba005"));
						return data;
					}
				});
	}

	
	
	public void setTxManager(PlatformTransactionManager txManager) {
		this.txManager = txManager;
	}



	public int getUnpostCount() {
		return this.getJdbcTemplate().queryForInt(
				SQL_GET_UNPOSTED_COUNT_SUPPINV);
	}
	
	public Object getByFlowNo(String flowNo) {
		Object result = null;
		try{
			result = this.getJdbcTemplate().queryForObject(SQL_GET_SUPPINV_BY_FLOWNO,
					new RowMapper<SuppInv>() {
						public SuppInv mapRow(ResultSet rs, int i)
								throws SQLException {
							SuppInv data = new SuppInv();
							data.setFlowNo(rs.getString("hba001"));
							data.setSuppNo(rs.getString("hba007"));
							data.setSuppName(rs.getString("bca002"));
							data.setBillDate(rs.getDate("hba005"));
							data.setItems(findItems(data.getFlowNo()));
							return data;
						}
					}, flowNo);
		}catch(EmptyResultDataAccessException e){ 
			result = OpResult.FAIL(String.format("单据(%s)不存在或者已过账或者已经上传过！", flowNo), 1);
		}
		return result;
	}

	public List findItems(String flowNo) {
		return this.getJdbcTemplate().query(SQL_GET_SUPPINVITEM_BY_FLOWNO, new RowMapper<SuppInvItem>() {
			public SuppInvItem mapRow(ResultSet rs, int i) throws SQLException {
				SuppInvItem data = new SuppInvItem();
				data.setItemNo(rs.getInt("hbb001"));
				data.setProdNo(rs.getString("hbb004"));
				data.setQty(rs.getDouble("qty"));
				data.setBatchNo(rs.getString("hbb021"));
				data.setDlvQty(rs.getDouble("hbb017"));
				return data;
			}
		}, flowNo);
	}
	
	public OpResult updateInfo(final Object bill){
		OpResult result = null;
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = txManager.getTransaction(def);
		try{
			result = updateInfoInternal(bill);
			txManager.commit(status);
		}catch(Exception ex){
			ex.printStackTrace();
			txManager.rollback(status);
			result = OpResult.UNKNOWNFAIL;
		}
		return result;
	}

	public OpResult updateInfoInternal(final Object bill) {
		SuppInv data = (SuppInv)bill;
		Object result = getByFlowNo(data.getFlowNo());
		if(result instanceof OpResult)
			return (OpResult)result;
		SuppInv dataDB = (SuppInv)result;
		StringBuffer msg = new StringBuffer("");
		for (SuppInvItem item : data.getItems()) {
			boolean found = false;
			for (SuppInvItem itemDB : dataDB.getItems()) {
				if (item.getItemNo() == itemDB.getItemNo()
						&& item.getProdNo().equals(itemDB.getProdNo())) {
					found = true;
					break;
				}
			}

			if (!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目在ERP中不存在",
						item.getItemNo(), item.getProdNo()));
		}

		if (msg.length() > 0) {
			msg.insert(0, String.format("上传的单据(%s)中", data.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 2);
		}

		for (SuppInvItem itemDB : dataDB.getItems()) {
			boolean found = false;
			for (SuppInvItem item : data.getItems()) {
				if (item.getItemNo() == itemDB.getItemNo()
						&& item.getProdNo().equals(itemDB.getProdNo())) {
					found = true;
					break;
				}
			}

			if (!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目没有更新",
						itemDB.getItemNo(), itemDB.getProdNo()));
		}

		if (msg.length() > 0) {
			msg.insert(0, String.format("ERP的单据(%s)中", dataDB.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 3);
		}
		
		for(SuppInvItem itemDB:dataDB.getItems()){
			double rcvQtyTotal = 0.0;
			for(SuppInvItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo()))
					rcvQtyTotal += item.getRcvQty();
			}
			
			if(rcvQtyTotal > itemDB.getQty())
				msg.append(String.format(
						",项目编号(%d)物料编号为(%s)的项目的接收数量大于可入库数量",
						itemDB.getItemNo(), itemDB.getProdNo()));
		}
		

		if (msg.length() > 0) {
			msg.insert(0, String.format("上传的单据(%s)中", dataDB.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 4);
		}
		
		if(StringUtils.isNotEmpty(data.getOperatorNo()))
			this.getJdbcTemplate().update("UPDATE shb01 SET hba015=?,hba100='1' WHERE hba001=?",data.getOperatorNo(),data.getFlowNo());
		else
			this.getJdbcTemplate().update("UPDATE shb01 SET hba100='1' WHERE hba001=?",data.getFlowNo());
		for(SuppInvItem itemDB:dataDB.getItems()){
			boolean updated = false;
			for(SuppInvItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					String parentWhNo = getParentWhNo(item.getWhLocNo());
					if(!updated){
						this.getJdbcTemplate().update(SQL_UPDATE_SUPPINVITEM, 
								parentWhNo,item.getRcvQty(),item.getBatchNo(),item.getWhLocNo(),item.getRcvQty(),item.getRcvQty(),item.getRcvQty(),item.getItemNo(),data.getFlowNo());
						updated = true;
					}else{
						copyInsert(data,item,parentWhNo);
					}
				}
					
			}
		}
		
		return OpResult.SUCCESS;
	}
	
	private String getParentWhNo(String whLocNo){
		List<String> l =this.getJdbcTemplate().queryForList("SELECT haa012 FROM sha01 WHERE haa001=?", String.class,whLocNo);
		if(l!=null && l.size()>0)
			return l.get(0);
		else
			return "";
	}
	
	private void copyInsert(SuppInv data,SuppInvItem item,String parentWhNo){
		int maxNo = this.getJdbcTemplate().queryForInt("SELECT MAX(hbb001) FROM shb02 WHERE hbb002=?",data.getFlowNo());
		int nextNo = maxNo+10;
		this.getJdbcTemplate().update(
				"INSERT INTO shb02(hbb001,hbb002,hbb003,hbb004,hbb005,hbb006,hbb007,hbb008,hbb009,hbb010," +
				"hbb011,hbb012,hbb013,hbb014,hbb015,hbb016,hbb017,hbb018,hbb019,hbb020," +
				"hbb021,hbb022,hbb023,hbb024,hbb025,hbb026,hbb027,hbb028,hbb029,hbb030," +
				"hbb031,hbb032,hbb033,hbb034,hbb035,hbb036,hbb037,hbb038,hbb039) " +
				"SELECT ?,hbb002,?,hbb004,hbb005,hbb006,hbb007,hbb008,hbb009,hbb010," +
				"hbb011,hbb012,hbb013,hbb014,hbb014*?,hbb016,hbb017,?,hbb019,hbb020," +
				"?,hbb022,?,hbb024,hbb025,hbb026,hbb027,hbb028,hbb029,hbb030," +
				"hbb030*?,hbb032,hbb033,hbb033*?,hbb035,hbb036,hbb037,hbb038,hbb039 FROM shb02 WHERE hbb001=? AND hbb002=?",
				nextNo,item.getWhLocNo(),item.getRcvQty(),item.getRcvQty(),item.getBatchNo(),parentWhNo,item.getRcvQty(),item.getRcvQty(),item.getItemNo(),data.getFlowNo());
	}

}
